﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using up7.db.utils;
using up7.filemgr.app;

namespace up7.db.sql
{
    public class SqlBuilder
    {
        public static SqlBuilder build() { return new SqlBuilder(); }
        /// <summary>
        /// 拼接SQL语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string select(string table, string fields, string where)
        {
            string sql_where = string.Empty;
            if (!string.IsNullOrEmpty(where.Trim())) sql_where = string.Format("where {0}", where);
            var sql = string.Format("select {0} from {1} {2}", fields, table, sql_where);
            return sql;
        }

        public string select(string table, string fields, SqlParam[] where)
        {
            var sql = string.Format("select {0} from {1} where {2}",
                fields,
                table,
                this.wheres(where));
            return sql;
        }

        public string select(string table, string fields, SqlWhere where)
        {
            var sql = string.Format("select {0} from {1} {2}",
                fields,
                table,
                where.toSqlWhere());
            return sql;
        }
        public string select(string table, SqlParam[] fields, SqlWhere where, SqlSort sort)
        {
            var sql = string.Format("select {0} from {1} {2} {3}",
                this.defines(fields),
                table,
                where.toSqlWhere(),
                sort.toSql());
            return sql;
        }

        public string select(string table, SqlParam[] fields, SqlWhere where, int top = 0)
        {
            if (ConfigReader.dbType() == DataBaseType.Oracle) return this.selectTopOracle(table, fields, where, top);
            else if (ConfigReader.dbType() == DataBaseType.MySQL) return this.selectTopMySQL(table, fields, where, top);

            string tops = string.Format(" top {0} ", top);
            if (0 == top) tops = "";

            var sql = string.Format("select {0} {1} from {2} {3}",
                tops,
                this.defines(fields),
                table,
                where.toSqlWhere());
            return sql;
        }

        public string select(string table, SqlParam[] fields, SqlParam[] where)
        {
            var sql = string.Format("select {0} from {1} where {2}",
                this.defines(fields),
                table,
                this.wheres(where));
            return sql;
        }

        public string selectTopMySQL(string table, SqlParam[] fields, SqlWhere where, int top = 0)
        {
            string tops = string.Format(" limit 0,{0} ", top);
            if (0 == top) tops = "";

            var sql = string.Format("select {0} from {1} {2} {3}",
                this.defines(fields),
                table,
                where.toSqlWhere(),
                tops);
            return sql;
        }
        public string selectTopOracle(string table, SqlParam[] fields, SqlWhere where, int top = 0)
        {
            string tops = string.Format(" and rownum <= {0} ", top);
            if (0 == top) tops = "";

            var sql = string.Format("select {0} from {1} {2} {3}",
                this.defines(fields),
                table,
                where.toSqlWhere(),
                tops);
            return sql;
        }

        public string insert(string table, SqlParam[] fields)
        {
            string sql = string.Format("insert into {0} ( {1} ) values( {2} )",
                table,
                this.defines(fields),
                this.prameters(fields)
                );
            return sql;
        }

        public string update(string table, SqlParam[] fields, SqlParam[] where)
        {
            string sql = string.Format("update {0} set {1} where {2}",
                table,
                this.setter(fields),
                this.wheres(where)
                );
            return sql;
        }

        public string update(string table, SqlSeter st, SqlWhere where)
        {
            string sql = string.Format("update {0} set {1} {2}",
                table,
                st.toSql(),
                where.toSqlWhere()
                );
            return sql;
        }

        public string update(string table, SqlSeter st, SqlParam[] where)
        {
            string sql = string.Format("update {0} set {1} where {2}",
                table,
                st.toSql(),
                this.wheres(where)
                );
            return sql;
        }

        public string update(string table, SqlParam[] fields, SqlWhere where)
        {
            string sql = string.Format("update {0} set {1} {1}",
                table,
                this.setter(fields),
                where.empty() ? "" : " where " + where.toSql()
                ); ;
            return sql;
        }

        public string delete(string table, SqlParam[] fields)
        {
            string sql = string.Format("delete from {0} where {1}",
                table,
                this.defines(fields),
                this.wheres(fields)
                );
            return sql;
        }

        public string delete(string table, SqlWhere where)
        {
            string sql = string.Format("delete from {0} {1}",
                table,
                where.toSqlWhere()
                );
            return sql;
        }

        /// <summary>
        /// 声明化,a,b,c,d,e,f,g
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public string defines(SqlParam[] ps)
        {
            return SqlParam.mergeNames(ps);
        }

        /// <summary>
        /// 将字段转换成变量模式,@a,@b,@c,@d
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public string prameters(SqlParam[] ps)
        {
            var dbt = ConfigReader.dbType();
            string tmp = "@{0}";
            if (dbt == DataBaseType.Oracle) tmp = ":{0}";
            else if (dbt == DataBaseType.Kingbase ||
                dbt == DataBaseType.ODBC) tmp = "?";
            return SqlParam.mergeNames(ps, tmp);
        }

        /// <summary>
        /// 将字段转换成赋值语句，a=@a,b=@b,c=@c,d=@d
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public string setter(SqlParam[] ps)
        {

            var dbt = ConfigReader.dbType();
            if (dbt == DataBaseType.Oracle)
                return SqlParam.mergeNames(ps, "{0}=:{0}");
            else if (dbt == DataBaseType.Kingbase ||
                dbt == DataBaseType.ODBC)
                return SqlParam.mergeNames(ps, "{0}=?");
            return SqlParam.mergeNames(ps, "{0}=@{0}");
        }

        /// <summary>
        /// 将变量转换成条件语句,a=@a and b=@b and c=@c
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public string wheres(SqlParam[] ps)
        {
            var dbt = ConfigReader.dbType();
            if (dbt == DataBaseType.Oracle)
                return SqlParam.mergeNames(ps, "{0}=:{0}", " and ");
            else if (dbt == DataBaseType.Kingbase ||
                dbt == DataBaseType.ODBC)
                return SqlParam.mergeNames(ps, "{0}=?", " and ");
            return SqlParam.mergeNames(ps, "{0}=@{0}", " and ");
        }
    }
}